package com.zerowidth.db.op;

import com.zerowidth.db.DBHelper;
import com.zerowidth.db.base.SqlResult;
import com.zerowidth.db.bean.OrderBean;
import com.zerowidth.db.utils.IOUtils;
import com.zerowidth.db.utils.TimeUtils;
import com.zerowidth.moc.web.utils.TimeUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class VipOrderDB {

    public static int countNoPay(String start_time, String end_time) {
        String sql = String.format("SELECT COUNT(*) FROM vip_order WHERE pay_success = 0 AND create_time >= '%s' " +
                " AND create_time <= '%s'", start_time, end_time);

        return DBHelper.queryCount(sql);
    }

    //用于支付宝订单查找
    public static OrderBean findByOutOrderNo(String outOrderNo) {
        String sql = String.format("SELECT * FROM vip_order WHERE out_trade_no='%s'", outOrderNo);

        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            ResultSet rs = sr.rs;
            if (rs.next()) {
                OrderBean orderBean = new OrderBean();
                orderBean.id = rs.getInt("id");
                orderBean.vip_type = rs.getInt("vip_type");
                orderBean.user_id = rs.getString("user_id");
                orderBean.stat_id = rs.getString("stat_id");
                orderBean.out_trade_no = rs.getString("out_trade_no");
                orderBean.amount = rs.getFloat("amount");
                orderBean.price = rs.getFloat("price");
                orderBean.subject_value = rs.getString("subject_value");
                orderBean.pay_success = rs.getBoolean("pay_success");
                orderBean.order_info = rs.getString("order_info");
                orderBean.create_time = rs.getString("create_time");
                orderBean.order_type = rs.getString("order_type");
                orderBean.phone_number = rs.getString("phone_number");

                return orderBean;
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }


    /**
     * 查询10分钟内 此类型、且未支付成功的会员订单
     * @param vipType
     * @return
     */
    public static List<OrderBean> findUnPayOrder(int vipType) {

        String time10Min = TimeUtils.convert(System.currentTimeMillis() - 10*60*1000, TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

        String sql = String.format("SELECT * FROM vip_order WHERE vip_type=%d AND pay_success = 0 AND create_time > '%s' ",
                vipType, time10Min);

        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            List<OrderBean> list = new ArrayList<>();

            ResultSet rs = sr.rs;
            while (rs.next()) {
                OrderBean orderBean = new OrderBean();
                orderBean.id = rs.getInt("id");
                orderBean.vip_type = rs.getInt("vip_type");
                orderBean.user_id = rs.getString("user_id");
                orderBean.stat_id = rs.getString("stat_id");
                orderBean.out_trade_no = rs.getString("out_trade_no");
                orderBean.amount = rs.getFloat("amount");
                orderBean.price = rs.getFloat("price");
                orderBean.subject_value = rs.getString("subject_value");
                orderBean.pay_success = rs.getBoolean("pay_success");
                orderBean.order_info = rs.getString("order_info");
                orderBean.create_time = rs.getString("create_time");
                orderBean.order_type = rs.getString("order_type");
                orderBean.phone_number = rs.getString("phone_number");

                list.add(orderBean);
            }

            return list;
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    //用于微信订单查找
    public static OrderBean findByOrderInfo(String preparedId) {
        String sql = String.format("SELECT * FROM vip_order WHERE order_info='%s'", preparedId);

        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            ResultSet rs = sr.rs;
            if (rs.next()) {
                OrderBean orderBean = new OrderBean();
                orderBean.id = rs.getInt("id");
                orderBean.vip_type = rs.getInt("vip_type");
                orderBean.user_id = rs.getString("user_id");
                orderBean.stat_id = rs.getString("stat_id");
                orderBean.out_trade_no = rs.getString("out_trade_no");
                orderBean.amount = rs.getFloat("amount");
                orderBean.price = rs.getFloat("price");
                orderBean.subject_value = rs.getString("subject_value");
                orderBean.pay_success = rs.getBoolean("pay_success");
                orderBean.order_info = rs.getString("order_info");
                orderBean.create_time = rs.getString("create_time");
                orderBean.order_type = rs.getString("order_type");
                orderBean.phone_number = rs.getString("phone_number");

                return orderBean;
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static boolean insert(OrderBean orderBean) {
        String time = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

        String sql = String.format("INSERT INTO vip_order(vip_type, order_info, user_id, stat_id, out_trade_no, amount, " +
                        " price, subject_value, pay_success, create_time, order_type, phone_number) " +
                        "VALUES(%d, '%s', '%s', '%s', '%s', %f, %f, '%s', %b, '%s', '%s', '%s')",
                orderBean.vip_type, orderBean.order_info, orderBean.user_id, orderBean.stat_id, orderBean.out_trade_no,
                orderBean.amount, orderBean.price, orderBean.subject_value, orderBean.pay_success, time,
                orderBean.order_type, orderBean.phone_number);

        return DBHelper.executeInsert(sql) > 0;
    }
    /**
     * 查询10分钟内 此类型、且未支付成功的会员订单
     * @param money
     * @return
     */
    public static OrderBean findMoneyPayOrder(String money) {

        String time10Min = TimeUtils.convert(System.currentTimeMillis() - 10*60*1000, TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

        String sql = String.format("SELECT * FROM vip_order WHERE ROUND(amount,2)=%s AND pay_success = 0 AND create_time > '%s' ",
                money, time10Min);

        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {

            ResultSet rs = sr.rs;
            if (rs.next()) {
                OrderBean orderBean = new OrderBean();
                orderBean.id = rs.getInt("id");
                orderBean.vip_type = rs.getInt("vip_type");
                orderBean.user_id = rs.getString("user_id");
                orderBean.stat_id = rs.getString("stat_id");
                orderBean.out_trade_no = rs.getString("out_trade_no");
                orderBean.amount = rs.getFloat("amount");
                orderBean.price = rs.getFloat("price");
                orderBean.subject_value = rs.getString("subject_value");
                orderBean.pay_success = rs.getBoolean("pay_success");
                orderBean.order_info = rs.getString("order_info");
                orderBean.create_time = rs.getString("create_time");
                orderBean.order_type = rs.getString("order_type");
                orderBean.phone_number = rs.getString("phone_number");

                return orderBean;
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    /**
     * 查询10分钟内 此类型、且未支付成功的会员订单
     * @param vipType
     * @return
     */
    public static OrderBean findMyUnPayOrder(int vipType, String userId) {

        String time10Min = TimeUtils.convert(System.currentTimeMillis() - 10*60*1000, TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

        String sql = String.format("SELECT * FROM vip_order WHERE vip_type=%d AND pay_success = 0 AND create_time > '%s' " +
                        " AND user_id = '%s'",
                vipType, time10Min, userId);

        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {

            ResultSet rs = sr.rs;
            if (rs.next()) {
                OrderBean orderBean = new OrderBean();
                orderBean.id = rs.getInt("id");
                orderBean.vip_type = rs.getInt("vip_type");
                orderBean.user_id = rs.getString("user_id");
                orderBean.stat_id = rs.getString("stat_id");
                orderBean.out_trade_no = rs.getString("out_trade_no");
                orderBean.amount = rs.getFloat("amount");
                orderBean.price = rs.getFloat("price");
                orderBean.subject_value = rs.getString("subject_value");
                orderBean.pay_success = rs.getBoolean("pay_success");
                orderBean.order_info = rs.getString("order_info");
                orderBean.create_time = rs.getString("create_time");
                orderBean.order_type = rs.getString("order_type");
                orderBean.phone_number = rs.getString("phone_number");

                return orderBean;
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }


    /**
     * 支付宝支付成功后，客户端拿到的是外部订单编号
     */
    public static boolean alipaySuccess(String out_trade_no) {
        String sql = String.format("UPDATE vip_order SET pay_success=1 WHERE out_trade_no='%s'", out_trade_no);

        return DBHelper.executeUpdate(sql) > 0;
    }

    /**
     * 微信支付成功后，客户端拿到的是 prepareid
     */
    public static boolean wxpaySuccess(String prepareid) {
        String sql = String.format("UPDATE vip_order SET pay_success=1 WHERE order_info='%s'", prepareid);

        return DBHelper.executeUpdate(sql) > 0;
    }

    /**
     * 扫码支付成功后，客户端拿到的是 金额
     */
    public static boolean qrPaySuccess(int id) {
        String timeNow = TimeUtil.now();

        String sql = String.format("UPDATE vip_order SET pay_success=1, pay_notify_time = '%s' WHERE id=%d", timeNow, id);

        return DBHelper.executeUpdate(sql) > 0;
    }

}
